﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace INSTA.BankSystem.DAL
{
    /// <summary>
    /// Classe de base abstraite pour toutes les classes qui 
    /// vont effectuer l'accès aux données de la BDD.
    /// </summary>
    public abstract class AbstractSqlGenerator
    {
        protected DataMapper _mappedObject;

        /// <summary>
        /// Ctor
        /// </summary>
        /// <param name="mappedObject">The mapped object.</param>
        protected AbstractSqlGenerator(DataMapper mappedObject)
        {
            _mappedObject = mappedObject;

        }

        /// <summary>
        /// Gets the mapped object.
        /// </summary>
        public DataMapper MappedObject
        {
            get         { return _mappedObject; }
            private set { _mappedObject = value; }
        }

        public virtual string GenerateSqlFor_SELECT()
        {
            var sb = new StringBuilder();

            var tableName       = MappedObject.GetMappedTableName();
            var primaryKeyName  = (from prop in MappedObject.MappedProperties.Values
                                   where prop.IsPrimaryKey == true
                                   select prop.PropertyFieldName).FirstOrDefault();

            var primaryKeyValue = (from prop in MappedObject.MappedProperties.Values
                                   where prop.IsPrimaryKey == true
                                   select prop.PropertyValue).FirstOrDefault();
            string formatString = "SELECT FROM [{0}] WHERE {1} = {2}";

            return string.Format(formatString,
                                 tableName,
                                 primaryKeyName,
                                 primaryKeyValue);
        }

        public virtual string GenerateSqlFor_DELETE()
        {
            return GenerateSqlFor_SELECT().Replace("SELECT", "DELETE");
        }

        public virtual string GenerateSqlFor_UPDATE()
        {
            var sb = new StringBuilder();

            var formatString = "UPDATE [{0}] SET ";
            sb.AppendFormat(formatString, MappedObject.GetMappedTableName());


            // On obtient la liste d'objets anonymes pour chacune des 
            // propriétés qui ne réprésente pas la clé primaire de la table.
            var query = from prop in MappedObject.MappedProperties.Values
                        where prop.IsPrimaryKey == false
                        select new
                                   {
                                       FieldName  = prop.PropertyFieldName,
                                       FieldValue = prop.PropertyValue
                                   };
            foreach (var element in query)
            {
                sb.AppendFormat("{0} = {1},",
                                element.FieldName,
                                element.FieldValue);
            }

            // On supprime la dernière virgule inutile..
            sb.Remove(sb.ToString().Length - 1, 1);

            formatString = " WHERE {0} = {1};";

            var query2 = (from prop in MappedObject.MappedProperties.Values
                          where prop.IsPrimaryKey
                          select prop).FirstOrDefault();

            sb.AppendFormat(formatString,
                            query2.PropertyFieldName,
                            query2.PropertyValue);

            return sb.ToString();
        }

         public virtual string GenerateSqlFor_INSERT()
         {
             var sb = new StringBuilder();

             // INSERT INTO [NomTable] (Champ1, Champ2)
             // VALUES (val1, val2);
             sb.AppendFormat("INSERT INTO [{0}] (",
                             MappedObject.GetMappedTableName());

             var query = from prop in MappedObject.MappedProperties.Values
                         where !prop.IsPrimaryKey
                         select new
                                    {
                                        FieldName = prop.PropertyFieldName,
                                        FieldValue = prop.PropertyValue,
                                    };
             foreach (var theField in query)
             {
                 sb.AppendFormat("{0},", theField.FieldName);
             }

             // On supprime la dernière virgule inutile..
             sb.Remove(sb.ToString().Length - 1, 1);

             sb.Append(") VALUES (");

             foreach (var theField in query)
             {
                 sb.AppendFormat("{0},", theField.FieldValue);
             }

             // On supprime la dernière virgule inutile..
             sb.Remove(sb.ToString().Length - 1, 1);

             sb.Append(");");


             return sb.ToString();
         }

         /// <summary>
         /// Gets the last generated primary key.
         /// </summary>
         /// <returns>the last generated primary key.</returns>
        public abstract string GetLastGeneratedPrimaryKey();

    }
}
